import string, sys, traceback
from datetime import date, timedelta
from mokocard.util.exceptionutil import printStack

try:
    from pysqlite2 import dbapi2 as sqlite3    
except:
    from sqlite3 import dbapi2 as sqlite3

from mokocard.obj.card import Card
from mokocard.util.logutil import LogUtil
from mokocard.obj.dbstatsdata import DbStatsData
from mokocard.obj.datecount import DateCount

class SqlUtil:

    connection = None
    readOnly = False
    reverse = False
    cards = []
    
    def isReadOnly(self):
        return self.readOnly

    def getConnection(self):
        if self.connection != None:
            return self.connection
        else:
            return None

    def getDbStats(self):

        if self.connection == None:
            LogUtil.writeLog("No Connection")
            return None
        
        # take database statistics
        dbStatsData = DbStatsData()
        
        #  count records
        cursor = self.connection.cursor()
        cursor.execute("select count(*), sum(ok_answer), sum(ko_answer) from card")
        data = cursor.fetchone()
        dbStatsData.setCountRecords(data[0])
        dbStatsData.setOkAnswers(data[1])
        dbStatsData.setKoAnswers(data[2])

        # number execution for date
        cursor.execute("select next_date, count(*) cnt from card group by next_date")
        for next_date, cnt in cursor:
            dateCount = DateCount(next_date, cnt)
            dbStatsData.addDateCount(dateCount)
        
        dbStatsData.setReadOnly(self.readOnly)
        dbStatsData.setReverse(self.reverse)
        
        return dbStatsData

    # delete records
    def deleteCards(self, idx):
        
        # read only mode no database update
        if self.readOnly == True:
            return
            
        row = self.connection.execute("delete from card where id = ?", (self.cards[idx].id,)).rowcount
        LogUtil.writeLog("deleted " + str(row) + " records - id " + str(self.cards[idx].id))
        self.connection.commit()
        return row

    def insertCard(self, question, answer):
        
        if self.connection == None:
            return "No Database Open"
        
        if self.readOnly == True:
            return "Database Read Only"

        try:
            cursor = self.connection.cursor()
            cursor.execute('insert into card (question, answer) values (?,?)',  (question, answer))
            self.connection.commit();
            cursor.close()
            return True
        except:
            LogUtil.writeLog(printStack(sys.exc_info()))
            return "No record inserted"

    # modify question/answer
    def changeCard(self, question, answer, idx):

        if self.connection == None:
            return "No Database Open"
        
        if self.readOnly == True:
            return "Database Read Only"

        try:
            cursor = self.connection.cursor()
            self.connection.execute("update card set question = ?, answer = ? where id = ?", (question, answer, self.cards[idx].id))
            self.connection.commit();
            cursor.close()
            return True
        except:
            LogUtil.writeLog(printStack(sys.exc_info()))
            printStack(sys.exc_info())
            return "No record changed"
    
    # modifiy stats
    def updateCard(self, idx, ok):
        
        # read only mode no database update
        if self.readOnly == True:
            return
            
        if ok:
            # great answer!
            nextDate = (date.today() + timedelta(2 ** self.cards[idx].iteration)).isoformat()
            self.connection.execute("update card set next_date = ?, iteration = iteration + 1, ok_answer = ok_answer + 1, last_answer = 1 where id = ?", (nextDate,self.cards[idx].id))
        else:
            # bad answer
            # next_date >> tomorrow
            nextDate = (date.today() + timedelta(1)).isoformat()
            self.connection.execute("update card set next_date = ?, iteration = 0, ko_answer = ko_answer + 1, last_answer = 0 where id = ?", (nextDate,self.cards[idx].id))

        self.connection.commit()
    
    def _openDatabase(self, pathDatabase):
        
        # reset current database reference
        self.pathDatabase = "None"
        
        # I never manage self.connection in this mode! ;)
        if self.connection != None:
            # close previous database
            self.closeDatabase()
            
        LogUtil.writeLog("try to load database " + pathDatabase)
        self.connection = sqlite3.connect(pathDatabase)
        
        # check existence of field last_answer
        try:        
            cursor = self.connection.cursor()
            cursor.execute("SELECT count(*) FROM sqlite_master where upper(sql) like '%LAST_ANSWER%' and name = 'card'")
            if cursor.fetchone()[0] == 0:
                LogUtil.writeLog("we need to alter table adding last_answer field")
                cursor.execute("alter table card add last_answer integer default 1")
            else:
                LogUtil.writeLog("nothing to alter")
        except: 
            LogUtil.writeLog(printStack(sys.exc_info()))
            LogUtil.writeLog("impossible to define existence of last_answer field")
            LogUtil.writeLog("add manually with these instructions:")
            LogUtil.writeLog("alter table card add last_answer integer default 1")
            return False

        # save current database reference
        self.pathDatabase = pathDatabase

    def createDatabase(self, pathDatabase):
        
        self._openDatabase(pathDatabase)
        
        if self.connection == None:
            return False
        
        try:
            LogUtil.writeLog("try to create table card")
            self.connection.execute("create table card (id integer primary key autoincrement, question varchar(255), answer varchar2(255), next_date varchar2(10) default CURRENT_DATE, iteration integer default 0, ok_answer integer default 0, ko_answer integer default 0, last_answer integer default 1)")
            # I have to understand if I need a commit for ddl
            self.connection.commit()
            LogUtil.writeLog("end creation table card")
            return True
        except:
            LogUtil.writeLog(printStack(sys.exc_info()))
            return False

    def openDatabase(self, pathDatabase, reverse, readOnly, all):

        try:
            self._openDatabase(pathDatabase)
            
            if self.connection == None:
                LogUtil.writeLog("no connection")
                return False
                
            # we have to update daily cards
            # every cards marked before sysdate move to sysdate
            if readOnly == False:
                self.markDailyCards()

            # load daily cursor
            print all
            self.cards = self.getDailyCards(reverse, all)
            self.readOnly = readOnly
            self.reverse = reverse
            return True
        except:
            LogUtil.writeLog(printStack(sys.exc_info()))
            print('exception')
            return False
        
        
    
    def markDailyCards(self):
        self.connection.execute("update card set next_date = current_date where next_date < current_date")
        self.connection.commit()
        return True     

    def getDailyCards(self, reverse, all):

        print str(reverse)
        print str(all)
        # empty cards array
        self.cards = []
        #for x in self.cards:
        #    print (x)

        print len(self.cards)
        
        if self.connection == None:
            LogUtil.writeLog("No Connection")
            return None
        
        cursor = self.connection.cursor()
        
        if all == False:
            vSysDate = string.join(date.today().isoformat(),"")
            cursor.execute("select * from card where next_date = ?", (vSysDate,))
        else:
            cursor.execute("select * from card")

        # return an array of card
        if reverse == False:
            for (id, question, answer, next_date, iteration, ok_answer, ko_answer, last_answer) in cursor:
                card = Card(id, question, answer, next_date, iteration, ok_answer, ko_answer, last_answer);
                self.cards.append(card)
        else:
            for (id, question, answer, next_date, iteration, ok_answer, ko_answer, last_answer) in cursor:
                # question > answer
                # answer > question
                card = Card(id, answer, question, next_date, iteration, ok_answer, ko_answer, last_answer);
                self.cards.append(card)

        cursor.close()
        return self.cards
        
    def getFilterCards(self, code):
        
        # empty cards array
        allCards = []

        print len(self.cards)
        
        if self.connection == None:
            LogUtil.writeLog("No Connection")
            return None
        
        cursor = self.connection.cursor()
        if code == None:
            cursor.execute("select * from card")
        elif code == "KO":
            cursor.execute("select * from card where last_answer = 0")


        # return an array of card
        for (id, question, answer, next_date, iteration, ok_answer, ko_answer, last_answer) in cursor:
            card = Card(id, question, answer, next_date, iteration, ok_answer, ko_answer, last_answer);
            allCards.append(card)
        
        cursor.close()
        return allCards
    
    def closeDatabase(self):
        
        if self.connection == None:
            return None
        
        self.connection.close()
        return True

    def __init__(self):
        None